/******************************************************************************/
/***           Generated by IBExpert 2007.11.25 14.03.08 13:52:48           ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1251;

CONNECT 'C:\myprojects\Etc\FONDS.FDB' USER 'SYSDBA' PASSWORD 'masterkey';



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR GEN_ETC;


SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE OR ALTER PROCEDURE SP_READ_CURRENT_USER
RETURNS (
    UID INTEGER,
    NAME VARCHAR(64) CHARACTER SET WIN1251,
    ID_TYPE_ACCES_RIGHT INTEGER,
    FULL_NAME VARCHAR(255) CHARACTER SET WIN1251,
    DESCRIPTIONS VARCHAR(255) CHARACTER SET WIN1251,
    IS_DELETED SMALLINT,
    IS_DISABLED SMALLINT)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_READ_ETC (
    OBJ VARCHAR(512) CHARACTER SET WIN1251,
    UID INTEGER)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_READ_ETC_ALL (
    UID INTEGER)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_READ_ETC_ALL2USER (
    UNAME VARCHAR(64) CHARACTER SET WIN1251)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_READ_ETC_PARAMETERS (
    OBJECT_ID INTEGER)
RETURNS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE BIGINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_READ_ETC_PARAMETERS2USER (
    OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    UNAME VARCHAR(64) CHARACTER SET WIN1251)
RETURNS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE SMALLINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_SET_ETC (
    ID INTEGER,
    OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    ID_USER INTEGER)
RETURNS (
    RESULT INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_SET_ETC2OBJ (
    UNAME VARCHAR(64) CHARACTER SET WIN1251,
    OLD_OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    NEW_OBJECT VARCHAR(512) CHARACTER SET WIN1251)
RETURNS (
    RESULT INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_SET_PARAMETERS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE BIGINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 4096)
RETURNS (
    RESULT INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SP_SET_RENAME_PARAMETER (
    ID INTEGER,
    NEW_NAME VARCHAR(250) CHARACTER SET WIN1251)
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE ETC (
    ID       INTEGER NOT NULL,
    OBJECT   VARCHAR(512) CHARACTER SET WIN1251 NOT NULL,
    ID_USER  INTEGER NOT NULL
);

CREATE TABLE ETC_PARAMETERS (
    ID            INTEGER NOT NULL,
    NAME          VARCHAR(250) CHARACTER SET WIN1251 NOT NULL,
    PARAM_TYPE    SMALLINT NOT NULL,
    ID_OBJECT     INTEGER NOT NULL,
    DATE_VALUE    TIMESTAMP,
    FLOAT_VALUE   DOUBLE PRECISION,
    INT_VALUE     BIGINT,
    STRING_VALUE  VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE    BLOB SUB_TYPE 0 SEGMENT SIZE 4096
);

CREATE TABLE USERS (
    UID                  INTEGER NOT NULL,
    NAME                 VARCHAR(64) CHARACTER SET WIN1251 NOT NULL,
    ID_TYPE_ACCES_RIGHT  INTEGER NOT NULL,
    FULL_NAME            VARCHAR(255) CHARACTER SET WIN1251,
    DESCRIPTIONS         VARCHAR(255) CHARACTER SET WIN1251,
    IS_DELETED           SMALLINT DEFAULT 0,
    IS_DISABLED          SMALLINT DEFAULT 0
);



/******************************************************************************/
/***                           Unique Constraints                           ***/
/******************************************************************************/

ALTER TABLE ETC ADD CONSTRAINT UNQ1_ETC UNIQUE (OBJECT, ID_USER);
ALTER TABLE USERS ADD UNIQUE (NAME);


/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE ETC ADD CONSTRAINT PK_ETC PRIMARY KEY (ID);
ALTER TABLE ETC_PARAMETERS ADD CONSTRAINT PK_ETC_PARAMETERS PRIMARY KEY (ID);
ALTER TABLE USERS ADD PRIMARY KEY (UID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE ETC_PARAMETERS ADD CONSTRAINT FK_ETC_PARAMETERS FOREIGN KEY (ID_OBJECT) REFERENCES ETC (ID) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX ETC_IDX_OBJ ON ETC (OBJECT);
CREATE INDEX ETC_IDX_USER ON ETC (ID_USER);
CREATE INDEX ETC_PARAMETERS_IDX1 ON ETC_PARAMETERS (ID, ID_OBJECT);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: ETC_ID_AUTOINCREMENT */
CREATE OR ALTER TRIGGER ETC_ID_AUTOINCREMENT FOR ETC
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_ETC,1);
END
^

/* Trigger: ETC_PARAMETERS_ID_AUTOINCREMENT */
CREATE OR ALTER TRIGGER ETC_PARAMETERS_ID_AUTOINCREMENT FOR ETC_PARAMETERS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_ETC,1);
END
^

SET TERM ; ^



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

CREATE OR ALTER PROCEDURE SP_READ_CURRENT_USER
RETURNS (
    UID INTEGER,
    NAME VARCHAR(64) CHARACTER SET WIN1251,
    ID_TYPE_ACCES_RIGHT INTEGER,
    FULL_NAME VARCHAR(255) CHARACTER SET WIN1251,
    DESCRIPTIONS VARCHAR(255) CHARACTER SET WIN1251,
    IS_DELETED SMALLINT,
    IS_DISABLED SMALLINT)
AS
BEGIN
  FOR
    SELECT USERS.UID, USERS.NAME, USERS.ID_TYPE_ACCES_RIGHT, USERS.FULL_NAME, USERS.DESCRIPTIONS, USERS.IS_DELETED, USERS.IS_DISABLED
    FROM USERS
    where (USERS.NAME=user)
    INTO :UID,
         :NAME,
         :ID_TYPE_ACCES_RIGHT,
         :FULL_NAME,
         :DESCRIPTIONS,
         :IS_DELETED,
         :IS_DISABLED
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_READ_ETC (
    OBJ VARCHAR(512) CHARACTER SET WIN1251,
    UID INTEGER)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
BEGIN
    --check user

    if (:uid=-1) then
        select uid from users where name=user into  :uid;

  FOR
   SELECT ETC.ID, ETC.OBJECT, ETC.ID_USER, USERS.NAME
   FROM USERS
   RIGHT OUTER JOIN ETC ON (USERS.UID = ETC.ID_USER)
   where etc.id_user=:uid and etc.object=:obj
    INTO :ID,
         :OBJECT,
         :ID_USER,
         :user_name
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_READ_ETC_ALL (
    UID INTEGER)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
BEGIN

    if (:uid<>-1) then
        select uid from users where name=user into  :uid;

  FOR
   SELECT ETC.ID, ETC.OBJECT, ETC.ID_USER, USERS.NAME
   FROM USERS
   RIGHT OUTER JOIN ETC ON (USERS.UID = ETC.ID_USER)
   where etc.id_user=:uid
    INTO :ID,
         :OBJECT,
         :ID_USER,
         :user_name
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_READ_ETC_ALL2USER (
    UNAME VARCHAR(64) CHARACTER SET WIN1251)
RETURNS (
    ID INTEGER,
    OBJECT VARCHAR(250) CHARACTER SET WIN1251,
    ID_USER INTEGER,
    USER_NAME VARCHAR(64) CHARACTER SET WIN1251)
AS
declare variable uid integer;
BEGIN


        select uid from users where name=:uname into  :uid;

  FOR
   SELECT ETC.ID, ETC.OBJECT, ETC.ID_USER, USERS.NAME
   FROM USERS
   RIGHT OUTER JOIN ETC ON (USERS.UID = ETC.ID_USER)
   where etc.id_user=:uid
    INTO :ID,
         :OBJECT,
         :ID_USER,
         :user_name
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_READ_ETC_PARAMETERS (
    OBJECT_ID INTEGER)
RETURNS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE BIGINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
BEGIN
  FOR
    select etc_parameters.id, etc_parameters.name, etc_parameters.param_type, etc_parameters.id_object, etc_parameters.date_value, etc_parameters.float_value, etc_parameters.int_value, etc_parameters.string_value, etc_parameters.blob_value
    from etc_parameters
    where etc_parameters.id_object=:object_id
    INTO :ID,
         :NAME,
         :PARAM_TYPE,
         :ID_OBJECT,
         :DATE_VALUE,
         :FLOAT_VALUE,
         :INT_VALUE,
         :STRING_VALUE,
         :BLOB_VALUE
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_READ_ETC_PARAMETERS2USER (
    OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    UNAME VARCHAR(64) CHARACTER SET WIN1251)
RETURNS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE SMALLINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
BEGIN
  FOR
    select etc_parameters.id, etc_parameters.name, etc_parameters.param_type, etc_parameters.id_object, etc_parameters.date_value, etc_parameters.float_value, etc_parameters.int_value, etc_parameters.string_value, etc_parameters.blob_value
        from users
            inner join etc on (users.uid = etc.id_user)
            inner join etc_parameters on (etc.id = etc_parameters.id_object)
        where 
         (
          (etc.object = :object)
           and 
          (users.name = :uname)
         )
    INTO :ID,
         :NAME,
         :PARAM_TYPE,
         :ID_OBJECT,
         :DATE_VALUE,
         :FLOAT_VALUE,
         :INT_VALUE,
         :STRING_VALUE,
         :BLOB_VALUE
  DO
  BEGIN
    SUSPEND;
  END
END
^

CREATE OR ALTER PROCEDURE SP_SET_ETC (
    ID INTEGER,
    OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    ID_USER INTEGER)
RETURNS (
    RESULT INTEGER)
AS
begin
  if (:id_user=-1) then SELECT USERS.UID FROM USERS where (USERS.NAME=user) INTO :id_user;
  if (:id < 1) then
        begin
           if(not(exists(select * from etc where etc.object=:object and etc.id_user=:id_user)))
            then
             begin
                result=gen_id(gen_etc,1);
                insert into ETC (id, object, ID_USER) values (:result, :object,:id_user);
             end
           else result=-1;
        end
      else
        if (:object ='') then
           begin
            delete from etc where id=:id;
            result=0;
           end
        else
            begin
             if((exists(select * from etc where etc.id=:id)))
               then
                if(not(exists(select * from etc where etc.object=:object and etc.id_user=:id_user))) then
                 begin
                   update etc set object=:object, id_user=:id_user where id=:id;
                   result=:id;
                 end
             else result=-1;
            end
end
^

CREATE OR ALTER PROCEDURE SP_SET_ETC2OBJ (
    UNAME VARCHAR(64) CHARACTER SET WIN1251,
    OLD_OBJECT VARCHAR(512) CHARACTER SET WIN1251,
    NEW_OBJECT VARCHAR(512) CHARACTER SET WIN1251)
RETURNS (
    RESULT INTEGER)
AS
declare variable uid integer;
declare variable oid integer;
begin
  uid=-1; oid=-1;
  select users.uid from users where users.name=:uname into :uid;
  if (:uid < 0) then
        result=-1;
  else
    begin
      select etc.id from etc where etc.object=:old_object and etc.id_user=:uid into :oid;
      if ((exists (select etc.id from etc where etc.object=:new_object and etc.id_user=:uid))) then result=-2;
      else
          select result from SP_SET_ETC(:oid,:new_object,:uid) into :result;

    end
end
^

CREATE OR ALTER PROCEDURE SP_SET_PARAMETERS (
    ID INTEGER,
    NAME VARCHAR(250) CHARACTER SET WIN1251,
    PARAM_TYPE SMALLINT,
    ID_OBJECT INTEGER,
    DATE_VALUE TIMESTAMP,
    FLOAT_VALUE DOUBLE PRECISION,
    INT_VALUE BIGINT,
    STRING_VALUE VARCHAR(255) CHARACTER SET WIN1251,
    BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 4096)
RETURNS (
    RESULT INTEGER)
AS
declare variable old_name varchar(255);
declare variable old_id integer;
declare variable old_id_object integer;
declare variable old_param_type smallint;
begin
    if (:id < 1) then
        begin
           result=gen_id(gen_etc,1);
           insert into etc_parameters(id, name,param_type,id_object,date_value,float_value,int_value,string_value,blob_value)
            values(:result,:name,:param_type,:id_object,:date_value,:float_value,:int_value,:string_value,:blob_value);
        end
    else
        if (:name ='') then
           begin
            delete from etc_parameters where id=:id;
            result=0;
           end
        else
           begin
             update etc_parameters set name=:name, param_type=:param_type, id_object=:id_object, date_value=:date_value,
              float_value=:float_value, int_value=:int_value,string_value=:string_value,blob_value=:blob_value
             where id=:id;
             result=:id;
            end
end
^

CREATE OR ALTER PROCEDURE SP_SET_RENAME_PARAMETER (
    ID INTEGER,
    NEW_NAME VARCHAR(250) CHARACTER SET WIN1251)
AS
begin
update etc_parameters set etc_parameters.name=:new_name where id=:id;
end
^


SET TERM ; ^


/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE TABLE USERS
' Таблица пользователей';



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE PROCEDURE SP_READ_CURRENT_USER
'Возвращает данные текущего пользователя';

DESCRIBE PROCEDURE SP_READ_ETC
'Процедура чтения объектов для текущего пользователя';

DESCRIBE PROCEDURE SP_SET_ETC
'Изменение   объектов';

DESCRIBE PROCEDURE SP_SET_ETC2OBJ
'Изменение   объектов по имени пользователя и по содержимому объекта';

DESCRIBE PROCEDURE SP_SET_RENAME_PARAMETER
'Переименование параметра';



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE GENERATOR GEN_ETC
'Генератор для TDbEtc';



/* Fields descriptions */

DESCRIBE FIELD ID TABLE ETC
'Идентификатор';

DESCRIBE FIELD OBJECT TABLE ETC
'Наименование объекта';

DESCRIBE FIELD ID_USER TABLE ETC
'Идентификатор пользователя';

DESCRIBE FIELD ID TABLE ETC_PARAMETERS
'Идентификатор параметра';

DESCRIBE FIELD NAME TABLE ETC_PARAMETERS
'Имя параметра';

DESCRIBE FIELD PARAM_TYPE TABLE ETC_PARAMETERS
'0            ptNone,         //неизвестный тип
1            ptBlob,         //любой большой бинарный объект, нужны подтипы (например, doc, pdf и т.п.)
2            ptBool,         //логический
3            ptCurrency,     //Денежный тип от -922337203685477.5808 до 922337203685477.5807  храним в ETC_INTS
4            ptDateTime,     //дата-время
5            ptFloat,        //вещественное
6            ptInt,          //целое
7            ptInt64,        //большое целое
8            ptShortString,  //короткая строка
9            ptString,       //строка храним в Блобе';

DESCRIBE FIELD ID_OBJECT TABLE ETC_PARAMETERS
'Ссылка на объект';

DESCRIBE FIELD DATE_VALUE TABLE ETC_PARAMETERS
'Значение - дата';

DESCRIBE FIELD FLOAT_VALUE TABLE ETC_PARAMETERS
'Значение - число с плавающей точкой';

DESCRIBE FIELD INT_VALUE TABLE ETC_PARAMETERS
'Значение - целое число';

DESCRIBE FIELD STRING_VALUE TABLE ETC_PARAMETERS
' Значение - короткая строка';

DESCRIBE FIELD BLOB_VALUE TABLE ETC_PARAMETERS
'Значение  - blob';

DESCRIBE FIELD NAME TABLE USERS
'Имя пользователя';

DESCRIBE FIELD ID_TYPE_ACCES_RIGHT TABLE USERS
'Глобальный идентификатор доступа к объектам системы - перекрывает все другие разрешающие';

DESCRIBE FIELD FULL_NAME TABLE USERS
'Полное имя пользователя
';

DESCRIBE FIELD IS_DISABLED TABLE USERS
'Блокировка пользователя';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


/* Privileges of users */
GRANT SELECT ON RDB$ROLES TO PUBLIC;

/* Privileges of triggers */
GRANT UPDATE, REFERENCES ON ETC TO TRIGGER ETC_ID_AUTOINCREMENT;
GRANT UPDATE, REFERENCES ON ETC_PARAMETERS TO TRIGGER ETC_PARAMETERS_ID_AUTOINCREMENT;

/* Privileges of procedures */
GRANT SELECT ON USERS TO PROCEDURE SP_READ_CURRENT_USER;
GRANT SELECT ON ETC TO PROCEDURE SP_READ_ETC;
GRANT SELECT ON USERS TO PROCEDURE SP_READ_ETC;
GRANT SELECT ON ETC TO PROCEDURE SP_READ_ETC_ALL2USER;
GRANT SELECT ON USERS TO PROCEDURE SP_READ_ETC_ALL2USER;
GRANT SELECT ON ETC_PARAMETERS TO PROCEDURE SP_READ_ETC_PARAMETERS;
GRANT SELECT ON ETC_PARAMETERS TO PROCEDURE SP_READ_ETC_PARAMETERS2USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON ETC TO PROCEDURE SP_SET_ETC;
GRANT SELECT, INSERT, UPDATE, DELETE ON ETC TO PROCEDURE SP_SET_ETC2OBJ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ETC_PARAMETERS TO PROCEDURE SP_SET_PARAMETERS;
GRANT SELECT, UPDATE ON ETC_PARAMETERS TO PROCEDURE SP_SET_RENAME_PARAMETER;
